package cumt.wxl.winterolympics.dao;

import cumt.wxl.winterolympics.bean.Admin;
import cumt.wxl.winterolympics.bean.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author XiaoLeiWei
 * DAO 类为增加数据库查询用户的功能
 *
 *
 */

public class DAO {
    // 登录验证
    public User login(Connection conn, User user) throws Exception{
        User resulterAdmin = null;
        // sql 查询语句
        String sql="select * from users where username=? and password=?";
        // 获得执行sql语句的对象
        PreparedStatement pstatement =conn.prepareStatement(sql);
        pstatement.setString(1, user.getUsername());
        pstatement.setString(2, user.getPassword());
        // 执行sql语句获得结果集
        ResultSet rs = pstatement.executeQuery();
        if(rs.next()){
            resulterAdmin = new User();
            resulterAdmin.setUsersname(rs.getString("username"));
            resulterAdmin.setPassword(rs.getString("password"));
        }
        return resulterAdmin;
    }

    // 修改密码查找用户
    public User searchUser(Connection conn,User user) throws Exception {
        User resulterAdmin = null;
        // sql 查询语句
        String sql="select * from users where username=?";
        // 获得执行sql语句的对象
        PreparedStatement pstatement =conn.prepareStatement(sql);
        pstatement.setString(1, user.getUsername());
        // 执行sql语句获得结果集
        ResultSet rs = pstatement.executeQuery();
        if(rs.next()){
            resulterAdmin = new User();
            resulterAdmin.setUsersname(rs.getString("username"));
        }

        return resulterAdmin;
    }

    // 注册验证
    public boolean register(Connection conn,User user) throws Exception {
        boolean flag = false;
        // sql 查询语句
        String sql="insert into users(username,password,userEmail)values(?,?,?)";
        // 获得执行sql语句的对象
        PreparedStatement pstatement =conn.prepareStatement(sql);
        pstatement.setString(1, user.getUsername());
        pstatement.setString(2, user.getPassword());
        pstatement.setString(3, user.getEmail());
        // 执行sql语句获得结果集
        int res = pstatement.executeUpdate();
        if(res > 0) {
            flag = true;
        }
        return flag;
    }

    //注销验证
    public boolean delete(Connection conn,User user) throws Exception{
        boolean flag = false;
        String sql = "delete from users where username = ?";
        PreparedStatement pstatement =conn.prepareStatement(sql);
        pstatement.setString(1, user.getUsername());
        int res = pstatement.executeUpdate();
        if(res > 0) {
            flag = true;
        }
        return flag;
    }
    //管理员登录
    public Admin login(Connection conn, Admin admin) throws Exception{
        Admin resulterAdmin = null;
        // sql 查询语句
        String sql="select * from admin where adminName=? and password=?";
        // 获得执行sql语句的对象
        PreparedStatement pstatement =conn.prepareStatement(sql);
        pstatement.setString(1, admin.getAdminName());
        pstatement.setString(2, admin.getPassword());
        // 执行sql语句获得结果集
        ResultSet rs = pstatement.executeQuery();
        if(rs.next()){
            resulterAdmin = new Admin();
            resulterAdmin.setAdminName(rs.getString("adminName"));
            resulterAdmin.setPassword(rs.getString("password"));
        }
        return resulterAdmin;
    }

    //用户遍历
    public List<User> getUser(Connection conn,String sql) throws SQLException {
        //查询语句的执行结果为一个表格，即多个学生对象的集合，所有可以创建数组列表对象来存储
        List<User> lst = new ArrayList<User>();
        //获取连接对象
        try {
            Statement st = conn.createStatement();
            // 执行sql语句获得结果集
            ResultSet rs = st.executeQuery(sql);
            //遍历结果集对象
            while(rs.next()){
                User user = new User();
                user.setUsersname(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("userEmail"));
                lst.add(user);
            }
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            //资源使用完之后，如果没有关闭，则需要关闭
            if (conn!=null){
                conn.close();
            }
        }
        return lst;
    }
    
}

